/*** 
This do-file produces the Median 2-Bedroom Rent State-level data we use from the 
CSV downloaded from Census.
***/

*-------------------------------------------------------------------------------
* Set up
*-------------------------------------------------------------------------------

* Set $root
project figstabs, root
if (r(buildrunning)==0) include "${root}/code/config_interactive.do"

* Create required folders
cap mkdir "${root}/data/derived/ACS 2014-2018 5-Year State"
cap mkdir "${root}/data/derived/ACS 2014-2018 5-Year State/Individual Variables"

*-------------------------------------------------------------------------------
* Load and clean raw data
*-------------------------------------------------------------------------------

project, uses("${root}/data/dvc/ACS 2014-2018 5-Year State/Median 2BR Rent/ACSDT5Y2018.B25031-2023-02-27T201959.csv") 
import delimited "${root}/data/dvc/ACS 2014-2018 5-Year State/Median 2BR Rent/ACSDT5Y2018.B25031-2023-02-27T201959.csv", varnames(1) clear

* Remove margin of error columns
drop *marginoferror

* Keep relevant row: 2-bedroom
keep if _n == 5
rename  *grouping median_rent

* Adjust format to long
rename (*estimate) (estimate*)
tostring estimateiowa, replace
tostring estimatesouthdakota, replace
tostring estimatepuertorico, replace
tostring estimatewestvirginia, replace

reshape long estimate, i(median_rent) j(state) string
drop median_rent

* Change variable type
replace estimate = subinstr(estimate, ",", "",.)
destring estimate, replace

replace state = proper(state)
replace state = "District of Columbia" if state == "Districtofcolumbia"
replace state = "Puerto Rico" if state == "Puertorico"
replace state = "Rhode Island" if state == "Rhodeisland"
replace state = "South Carolina" if state == "Southcarolina"
replace state = "North Carolina" if state == "Northcarolina"
replace state = "West Virginia" if state == "Westvirginia"
replace state = "New Jersey" if state == "Newjersey"
replace state = "North Dakota" if state == "Northdakota"
replace state = "South Dakota" if state == "Southdakota"
replace state = "New York" if state == "Newyork"
replace state = "New Hampshire" if state == "Newhampshire"
replace state = "New Mexico" if state == "Newmexico"
replace state = "West Virginia" if state == "Westvirginia"

* Drop Puerto Rico and get fips 
drop if state == "Puerto Rico"
statastates, name(state) 
assert _merge == 3
drop _merge 

* Clean
rename estimate med_2br_rent_2018
rename state state_name

*-------------------------------------------------------------------------------
* Save file
*-------------------------------------------------------------------------------

save "${root}/data/derived/ACS 2014-2018 5-Year State/Individual Variables/ACS 2014-2018 Median 2BR Rent.dta", replace
project, creates("${root}/data/derived/ACS 2014-2018 5-Year State/Individual Variables/ACS 2014-2018 Median 2BR Rent.dta")
